﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Collections;

namespace Reviewer {
    public class DBHelper {
        private string CONNSTRING = "Data Source=Local.db;Pooling=true;FailIfMissing=false";

        public DBHelper() {

        }

        public void AddGame(string bsid, string gameType, DateTime gameDate, int orderID, string whitePlayer, string blackPlayer, string gameResult, string whiteTime, string blackTime, string moves, int totalMoves) {
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "INSERT INTO Games(BSID,GameType,GameDate,OrderID,WhitePlayer,BlackPlayer,GameResult,WhiteTime,BlackTime,Moves,TotalMoves) Values(@BSID,@GameType,@GameDate,@OrderID,@WhitePlayer,@BlackPlayer,@GameResult,@WhiteTime,@BlackTime,@Moves,@TotalMoves)";
                    cmd.Parameters.Add(new SQLiteParameter("BSID", bsid));
                    cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                    cmd.Parameters.Add(new SQLiteParameter("GameDate", gameDate));
                    cmd.Parameters.Add(new SQLiteParameter("OrderID", orderID));
                    cmd.Parameters.Add(new SQLiteParameter("WhitePlayer", whitePlayer));
                    cmd.Parameters.Add(new SQLiteParameter("BlackPlayer", blackPlayer));
                    cmd.Parameters.Add(new SQLiteParameter("GameResult", gameResult));
                    cmd.Parameters.Add(new SQLiteParameter("WhiteTime", whiteTime));
                    cmd.Parameters.Add(new SQLiteParameter("BlackTime", blackTime));
                    cmd.Parameters.Add(new SQLiteParameter("Moves", moves));
                    cmd.Parameters.Add(new SQLiteParameter("TotalMoves", totalMoves));
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public void AddOpenings(ArrayList moves, string bsid, string gameType, string gameResult) {
            int[,] tmpPieces = new int[26, 2];
            int[] tmpTop = new int[26];
            Move mov = moves[0] as Move;
            string move;
            int count;
            for(int i = 0; i < 26; i++) {
                tmpPieces[i, 0] = Constants.PIECES_POS_STACK;
                tmpPieces[i, 1] = Constants.PIECES_POS_STACK;
                tmpTop[i] = 0;
            }

            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteTransaction trans = conn.BeginTransaction()) {
                    using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                        for(int i = 0; i < moves.Count; i++) {
                            mov = moves[i] as Move;
                            move = mov.PieceIndex + "," + mov.FromX + "," + mov.FromY + "," + mov.ToX + "," + mov.ToY;
                            
                            cmd.CommandText = "INSERT INTO Openings(BSID,GameType,PID,MoveSide,Move,GameResult) Values(@BSID,@GameType,@PID,@MoveSide,@Move,@GameResult)";
                            cmd.Parameters.Add(new SQLiteParameter("BSID", bsid));
                            cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                            cmd.Parameters.Add(new SQLiteParameter("PID", FrmMain.GetPositionID(tmpPieces, tmpTop)));
                            cmd.Parameters.Add(new SQLiteParameter("MoveSide", mov.PieceIndex < 13 ? Constants.SIDE_WHITE : Constants.SIDE_BLACK));
                            cmd.Parameters.Add(new SQLiteParameter("Move", move));
                            cmd.Parameters.Add(new SQLiteParameter("GameResult", gameResult));
                            cmd.ExecuteNonQuery();

                            count = FrmMain.HasPiece(tmpPieces, mov.ToX, mov.ToY);
                            tmpTop[mov.PieceIndex] = count + 1;
                            tmpPieces[mov.PieceIndex, 0] = mov.ToX;
                            tmpPieces[mov.PieceIndex, 1] = mov.ToY;
                        }
                    }
                    trans.Commit();
                }
                conn.Close();
            }
        }

        public bool HasGame(string bsid) {
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "SELECT BSID FROM Games WHERE BSID=@BSID";
                    cmd.Parameters.Add(new SQLiteParameter("BSID", bsid));
                    if(cmd.ExecuteScalar() != null) {
                        return true;
                    }
                }
                conn.Close();
            }
            return false;
        }

        public void DeleteGame(string bsid) {
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "DELETE FROM Games WHERE BSID=@BSID";
                    cmd.Parameters.Add(new SQLiteParameter("BSID", bsid));
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public void DeleteOpening(string bsid) {
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "DELETE FROM Openings WHERE BSID=@BSID";
                    cmd.Parameters.Add(new SQLiteParameter("BSID", bsid));
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public DataTable GetAllGames(string gameType) {
            DataSet ds = new DataSet();
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    if(gameType != "all") {
                        cmd.CommandText = "SELECT * FROM Games WHERE GameType=@GameType ORDER BY GameDate DESC,OrderID DESC";
                        cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                    } else {
                        cmd.CommandText = "SELECT * FROM Games ORDER BY GameDate DESC,OrderID DESC";
                    }
                    using(SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                        da.Fill(ds);
                    }
                }
                conn.Close();
            }
            return ds.Tables[0];
        }

        public DataTable FilterGames(string keyword, string gameType) {
            string[] keywords = keyword.Split(' ');
            DataSet ds = new DataSet();
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    StringBuilder sql = new StringBuilder();
                    sql.Append("SELECT * FROM Games WHERE ");
                    for(int i = 0; i < keywords.Length; i++) {
                        if(i > 0) {
                            sql.Append(" AND ");
                        }
                        sql.Append("BSID LIKE '%" + keywords[i] + "%'");
                    }
                    if(gameType != "all") {
                        sql.Append(" AND GameType=@GameType");
                        cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                    }
                    sql.Append(" ORDER BY GameDate DESC,OrderID DESC");
                    cmd.CommandText = sql.ToString();
                    using(SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                        da.Fill(ds);
                    }
                }
                conn.Close();
            }
            return ds.Tables[0];
        }

        public long GetTotalGamesCount() {
            long count = 0;
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "SELECT COUNT(*) FROM Games";
                    object obj = cmd.ExecuteScalar();
                    if(obj != null) {
                        return long.Parse(obj.ToString());
                    }
                }
                conn.Close();
            }
            return count;
        }

        public long GetTotalFavGamesCount() {
            long count = 0;
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "SELECT COUNT(*) FROM Favourites";
                    object obj = cmd.ExecuteScalar();
                    if(obj != null) {
                        return long.Parse(obj.ToString());
                    }
                }
                conn.Close();
            }
            return count;
        }

        public DataTable GetMoves(string pid, int moveSide, string gameType) {
            DataSet ds = new DataSet();
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "SELECT DISTINCT Move, COUNT(Move) AS CM, \"\" AS MoveName, \"0\" AS White, \"0\" AS Draw, \"0\" AS Black FROM Openings WHERE PID=@PID AND MoveSide=@MoveSide AND GameType=@GameType GROUP by Move ORDER BY CM DESC";
                    cmd.Parameters.Add(new SQLiteParameter("PID", pid));
                    cmd.Parameters.Add(new SQLiteParameter("MoveSide", moveSide));
                    cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                    using(SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                        da.Fill(ds);
                    }

                    DataTable dt = ds.Tables[0];
                    DataTable tmpDT;
                    if(dt.Rows.Count > 0) {
                        for(int i = 0; i < dt.Rows.Count; i++) {
                            tmpDT = GetMoveResultCountInfo(pid, dt.Rows[i]["Move"].ToString(), gameType);
                            for(int j = 0; j < tmpDT.Rows.Count; j++) {
                                if(tmpDT.Rows[j]["GameResult"].ToString() == "1-0") {
                                    dt.Rows[i]["White"] = tmpDT.Rows[j][1];
                                    continue;
                                }
                                if(tmpDT.Rows[j]["GameResult"].ToString() == "1/2-1/2") {
                                    dt.Rows[i]["Draw"] = tmpDT.Rows[j][1];
                                    continue;
                                }
                                if(tmpDT.Rows[j]["GameResult"].ToString() == "0-1") {
                                    dt.Rows[i]["Black"] = tmpDT.Rows[j][1];
                                    continue;
                                }
                            }
                        }

                        float wwc = 0;
                        float bwc = 0;
                        float dc = 0;
                        float total = 0;

                        for(int i = 0; i < dt.Rows.Count; i++) {
                            wwc = float.Parse(dt.Rows[i]["White"].ToString());
                            bwc = float.Parse(dt.Rows[i]["Black"].ToString());
                            dc = float.Parse(dt.Rows[i]["Draw"].ToString());
                            total = wwc + bwc + dc;

                            dt.Rows[i]["White"] = (total != 0) ? String.Format("{0:#0.00%}", wwc / total) + " (" + wwc.ToString() + ")" : "0.00%(0)";
                            dt.Rows[i]["Draw"] = (total != 0) ? String.Format("{0:#0.00%}", dc / total) + " (" + dc.ToString() + ")" : "0.00%(0)";
                            dt.Rows[i]["Black"] = (total != 0) ? String.Format("{0:#0.00%}", bwc / total) + " (" + bwc.ToString() + ")" : "0.00%(0)";
                        }
                    }
                }
                conn.Close();
            }
            return ds.Tables[0];
        }

        private DataTable GetMoveResultCountInfo(string pid, string move, string gameType) {
            DataSet ds = new DataSet();
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "SELECT GameResult,COUNT(GameResult) AS GameResultCount FROM Openings WHERE PID=@PID AND Move=@Move AND GameType=@GameType GROUP BY GameResult";
                    cmd.Parameters.Add(new SQLiteParameter("PID", pid));
                    cmd.Parameters.Add(new SQLiteParameter("Move", move));
                    cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                    using(SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                        da.Fill(ds);
                    }
                }
                conn.Close();
            }
            return ds.Tables[0];
        }

        public DataTable GetListGames(string pid, string move, string gameType, string gameResult) {
            DataSet ds = new DataSet();
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    if(gameResult == string.Empty) {
                        cmd.CommandText = "SELECT DISTINCT Openings.BSID FROM Openings LEFT OUTER JOIN Games ON Openings.BSID=Games.BSID WHERE Openings.PID=@PID AND Openings.Move=@Move AND Openings.GameType=@GameType ORDER BY Games.GameDate DESC";
                    } else {
                        cmd.CommandText = "SELECT DISTINCT Openings.BSID FROM Openings LEFT OUTER JOIN Games ON Openings.BSID=Games.BSID WHERE Openings.PID=@PID AND Openings.Move=@Move AND Openings.GameType=@GameType AND Openings.GameResult=@GameResult ORDER BY Games.GameDate DESC";
                        cmd.Parameters.Add(new SQLiteParameter("GameResult", gameResult));
                    }
                    cmd.Parameters.Add(new SQLiteParameter("PID", pid));
                    cmd.Parameters.Add(new SQLiteParameter("Move", move));
                    cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                    using(SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                        da.Fill(ds);
                    }
                }
                conn.Close();
            }
            return ds.Tables[0];
        }

        public DataTable GetListGames(string pid, string gameType, string gameResult) {
            DataSet ds = new DataSet();
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    if(gameResult == string.Empty) {
                        cmd.CommandText = "SELECT DISTINCT Openings.BSID FROM Openings LEFT OUTER JOIN Games ON Openings.BSID=Games.BSID WHERE Openings.PID=@PID AND Openings.GameType=@GameType ORDER BY Games.GameDate DESC";
                    } else {
                        cmd.CommandText = "SELECT DISTINCT Openings.BSID FROM Openings LEFT OUTER JOIN Games ON Openings.BSID=Games.BSID WHERE Openings.PID=@PID AND Openings.GameType=@GameType AND Openings.GameResult=@GameResult ORDER BY Games.GameDate DESC";
                        cmd.Parameters.Add(new SQLiteParameter("GameResult", gameResult));
                    }
                    cmd.Parameters.Add(new SQLiteParameter("PID", pid));
                    cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                    using(SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                        da.Fill(ds);
                    }
                }
                conn.Close();
            }
            return ds.Tables[0];
        }

        public DataTable GetGameInfo(string bsid) {
            DataSet ds = new DataSet();
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "SELECT * FROM Games WHERE BSID=@BSID";
                    cmd.Parameters.Add(new SQLiteParameter("BSID", bsid));
                    using(SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                        da.Fill(ds);
                    }
                }
                conn.Close();
            }
            return ds.Tables[0];
        }

        public void AddFavourites(string bsid) {
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "SELECT BSID FROM Favourites WHERE BSID=@BSID";
                    cmd.Parameters.Add(new SQLiteParameter("BSID", bsid));
                    if(cmd.ExecuteScalar() != null) {
                        return;
                    }

                    cmd.CommandText = "INSERT INTO Favourites(BSID) Values(@BSID)";
                    cmd.Parameters.Add(new SQLiteParameter("BSID", bsid));
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public void DeleteFavGame(string bsid) {
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    cmd.CommandText = "DELETE FROM Favourites WHERE BSID=@BSID";
                    cmd.Parameters.Add(new SQLiteParameter("BSID", bsid));
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

        public DataTable GetAllFavGames(string gameType) {
            DataSet ds = new DataSet();
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    if(gameType != "all") {
                        cmd.CommandText = "SELECT * FROM Favourites F LEFT OUTER JOIN Games G ON F.BSID=G.BSID WHERE G.GameType=@GameType ORDER BY G.GameDate DESC,G.OrderID DESC";
                        cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                    } else {
                        cmd.CommandText = "SELECT * FROM Favourites F LEFT OUTER JOIN Games G ON F.BSID=G.BSID ORDER BY G.GameDate DESC,G.OrderID DESC";
                    }
                    using(SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                        da.Fill(ds);
                    }
                }
                conn.Close();
            }
            return ds.Tables[0];
        }

        public DataTable FilterFavGames(string keyword, string gameType) {
            string[] keywords = keyword.Split(' ');
            DataSet ds = new DataSet();
            using(SQLiteConnection conn = new SQLiteConnection(CONNSTRING)) {
                conn.Open();
                using(SQLiteCommand cmd = new SQLiteCommand(conn)) {
                    StringBuilder sql = new StringBuilder();
                    sql.Append("SELECT * FROM Favourites F LEFT OUTER JOIN Games G ON F.BSID=G.BSID WHERE ");
                    for(int i = 0; i < keywords.Length; i++) {
                        if(i > 0) {
                            sql.Append(" AND ");
                        }
                        sql.Append("G.BSID LIKE '%" + keywords[i] + "%'");
                    }
                    if(gameType != "all") {
                        sql.Append(" AND G.GameType=@GameType");
                        cmd.Parameters.Add(new SQLiteParameter("GameType", gameType));
                    }
                    sql.Append(" ORDER BY G.GameDate DESC,G.OrderID DESC");
                    cmd.CommandText = sql.ToString();
                    using(SQLiteDataAdapter da = new SQLiteDataAdapter(cmd)) {
                        da.Fill(ds);
                    }
                }
                conn.Close();
            }
            return ds.Tables[0];
        }
    }
}
